#!/usr/bin/perl

use DBI;

my $db = "va_stratlab";
my $ip = "localhost";
my $user = "va";
my $password = "dummy";

if ( $#ARGV + 1 == 0 ) {
    print "Usage: csv2sql.pl <zip file>\n";
    exit 0;
}

$ARGV[0] =~ m/^([a-zA-Z]+)_.*$/;
my $exchange = $1; 

unless ( -e $ARGV[0] ) {
    print $ARGV[0]." is not file\n";
    exit 0;
}

my $dbh=DBI->connect("dbi:mysql:$db:$ip", $user, $password) or die "Can't connect to DB";

print "unzip ".$ARGV[0]." ...\n";
@args = ("unzip", $ARGV[0]);
system(@args);

opendir(DIR, ".") or die "can't read directory$!";

while ( defined( $file = readdir(DIR) ) ) {
    next if ( $file =~ /^\.\.?$/ || $file !~ /^.+\.txt?$/ );

    open(FILEREAD, $file) || break;

    my $header = 1;
    my @cols;
    my $i = 0;
    my $table = {'stockname' => (), 'stockprice' => ()};

    print "process ".$file."...\n";
    while ( my $line = <FILEREAD> ) {
	chomp $line;
	if ( $header ) {
	    $#{$table{'stockname'}} = -1;
	    $#{$table{'stockprice'}} = -1;
	    $line =~ m/<(.+)>/;
	    @cols = split(">,<", $1);

	    for ( my $k = 0; $k <= $#cols; $k++ ) {
		if ( $k < 2 ) {
		    push @{$table{'stockname'}}, $cols[$k];
		} else {
		    push @{$table{'stockprice'}}, $cols[$k];
		}
	    }

	    $header = 0;
	    next;
	}

	my @value = split(",", $line);

	my $sth = $dbh->prepare("SELECT id FROM stockname WHERE exchange=? AND name=? AND code=?");
	$sth->bind_param(1, $exchange);
	$sth->bind_param(2, $value[0]);
	$sth->bind_param(3, $value[1]);
    	my $ret = $sth->execute;
	my $id = 0;
	if ( @ret = $sth->fetchrow_array() ) {
	    $id = $ret[0];
	} else {
	    $sth = $dbh->prepare("INSERT INTO stockname (exchange, name, code) VALUES (?, ?, ?)");
	    $sth->bind_param(1, $exchange);
	    $sth->bind_param(2, $value[0]);
	    $sth->bind_param(3, $value[1]);
	    $ret = $sth->execute;

	    $sth = $dbh->prepare("SELECT id FROM stockname WHERE exchange=? AND name=? AND code=?");
	    $sth->bind_param(1, $exchange);
	    $sth->bind_param(2, $value[0]);
	    $sth->bind_param(3, $value[1]);
	    $ret = $sth->execute;
	    if ( @ret = $sth->fetchrow_array() ) {
		$id = $ret[0];
	    }
	}

	$sql = "INSERT INTO stockprice (stock_id, ".join(",", @{$table{'stockprice'}}).") VALUES (".$id;
	for ( my $k = 2; $k <= $#cols; $k++ ) {
	    $sql .= ",'".$value[$k]."'";
	}
	$sql .= ")";
	$sth = $dbh->prepare($sql);
    	$ret = $sth->execute;
    }

    close(FILEREAD);
    unlink $file;
}
closedir(DIR);

print "done\n\n";

1;
